package org.convallaria.infrastruct.db.util;

import lombok.extern.slf4j.Slf4j;

import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicLong;
import java.util.regex.Pattern;

/**
 * 数据库异常分析工具
 * 
 * 功能特性：
 * - 异常分类和统计
 * - 异常趋势分析
 * - 异常解决建议
 * - 异常告警阈值监控
 * 
 * @author gjh
 * @since 2024-09-24
 */
@Slf4j
public class ExceptionAnalyzer {

    // 异常统计信息
    private static final Map<String, ExceptionStats> exceptionStats = new ConcurrentHashMap<>();
    private static final AtomicLong totalExceptions = new AtomicLong(0);
    
    // 异常模式匹配
    private static final Pattern CONNECTION_PATTERN = Pattern.compile("connection|timeout|refused", Pattern.CASE_INSENSITIVE);
    private static final Pattern SYNTAX_PATTERN = Pattern.compile("syntax|grammar|parse", Pattern.CASE_INSENSITIVE);
    private static final Pattern CONSTRAINT_PATTERN = Pattern.compile("constraint|duplicate|unique|foreign", Pattern.CASE_INSENSITIVE);
    private static final Pattern DEADLOCK_PATTERN = Pattern.compile("deadlock|lock.*timeout", Pattern.CASE_INSENSITIVE);
    private static final Pattern PERMISSION_PATTERN = Pattern.compile("access.*denied|permission|privilege", Pattern.CASE_INSENSITIVE);

    /**
     * 异常统计信息
     */
    public static class ExceptionStats {
        private final AtomicLong count = new AtomicLong(0);
        private volatile LocalDateTime firstOccurrence;
        private volatile LocalDateTime lastOccurrence;
        private volatile String lastSql;
        private volatile String lastMethod;

        public long getCount() { return count.get(); }
        public LocalDateTime getFirstOccurrence() { return firstOccurrence; }
        public LocalDateTime getLastOccurrence() { return lastOccurrence; }
        public String getLastSql() { return lastSql; }
        public String getLastMethod() { return lastMethod; }

        public void record(String sql, String method) {
            count.incrementAndGet();
            LocalDateTime now = LocalDateTime.now();
            
            if (firstOccurrence == null) {
                firstOccurrence = now;
            }
            lastOccurrence = now;
            lastSql = sql;
            lastMethod = method;
        }
    }

    /**
     * 异常类型枚举
     */
    public enum ExceptionType {
        CONNECTION("连接异常", "检查数据库连接配置、连接池设置、网络状况"),
        SYNTAX("SQL语法异常", "检查SQL语句语法、表名字段名是否正确"),
        CONSTRAINT("约束异常", "检查数据完整性约束、唯一键冲突、外键约束"),
        DEADLOCK("死锁异常", "优化事务逻辑、减少锁竞争、调整事务隔离级别"),
        PERMISSION("权限异常", "检查数据库用户权限、表访问权限"),
        TIMEOUT("超时异常", "优化SQL性能、调整超时配置、检查系统负载"),
        DATA("数据异常", "检查数据类型匹配、字段长度限制、数据格式"),
        UNKNOWN("未知异常", "查看详细异常信息，联系技术支持");

        private final String description;
        private final String suggestion;

        ExceptionType(String description, String suggestion) {
            this.description = description;
            this.suggestion = suggestion;
        }

        public String getDescription() { return description; }
        public String getSuggestion() { return suggestion; }
    }

    /**
     * 分析异常并记录统计信息
     */
    public static ExceptionType analyzeException(Throwable exception, String sql, String method) {
        if (exception == null) {
            return ExceptionType.UNKNOWN;
        }

        try {
            totalExceptions.incrementAndGet();
            
            // 分类异常
            ExceptionType type = classifyException(exception);
            
            // 记录统计信息
            String key = type.name() + ":" + exception.getClass().getSimpleName();
            exceptionStats.computeIfAbsent(key, k -> new ExceptionStats())
                         .record(sql, method);
            
            // 记录详细日志
            logExceptionAnalysis(exception, type, sql, method);
            
            return type;
            
        } catch (Exception e) {
            log.warn("分析异常时发生错误", e);
            return ExceptionType.UNKNOWN;
        }
    }

    /**
     * 分类异常类型
     */
    private static ExceptionType classifyException(Throwable exception) {
        String exceptionName = exception.getClass().getName().toLowerCase();
        String message = exception.getMessage() != null ? exception.getMessage().toLowerCase() : "";
        
        // 1. 连接相关异常
        if (exceptionName.contains("connection") || 
            exceptionName.contains("timeout") ||
            CONNECTION_PATTERN.matcher(message).find()) {
            return ExceptionType.CONNECTION;
        }
        
        // 2. SQL语法异常
        if (exceptionName.contains("syntax") ||
            exceptionName.contains("grammar") ||
            SYNTAX_PATTERN.matcher(message).find()) {
            return ExceptionType.SYNTAX;
        }
        
        // 3. 约束违反异常
        if (exceptionName.contains("constraint") ||
            exceptionName.contains("duplicate") ||
            exceptionName.contains("integrity") ||
            CONSTRAINT_PATTERN.matcher(message).find()) {
            return ExceptionType.CONSTRAINT;
        }
        
        // 4. 死锁异常
        if (exceptionName.contains("deadlock") ||
            DEADLOCK_PATTERN.matcher(message).find()) {
            return ExceptionType.DEADLOCK;
        }
        
        // 5. 权限异常
        if (exceptionName.contains("access") ||
            exceptionName.contains("permission") ||
            PERMISSION_PATTERN.matcher(message).find()) {
            return ExceptionType.PERMISSION;
        }
        
        // 6. 超时异常
        if (exceptionName.contains("timeout") ||
            message.contains("timeout")) {
            return ExceptionType.TIMEOUT;
        }
        
        // 7. 数据相关异常
        if (exceptionName.contains("data") ||
            exceptionName.contains("type") ||
            exceptionName.contains("conversion")) {
            return ExceptionType.DATA;
        }
        
        // 8. SQL异常的特殊处理
        if (exception instanceof SQLException) {
            SQLException sqlEx = (SQLException) exception;
            return classifyBySqlState(sqlEx.getSQLState(), sqlEx.getErrorCode());
        }
        
        return ExceptionType.UNKNOWN;
    }

    /**
     * 根据SQL状态码分类异常
     */
    private static ExceptionType classifyBySqlState(String sqlState, int errorCode) {
        if (sqlState == null) {
            return ExceptionType.UNKNOWN;
        }
        
        // SQL状态码分类
        if (sqlState.startsWith("08")) { // 连接异常
            return ExceptionType.CONNECTION;
        } else if (sqlState.startsWith("42")) { // 语法错误或访问权限
            return ExceptionType.SYNTAX;
        } else if (sqlState.startsWith("23")) { // 约束违反
            return ExceptionType.CONSTRAINT;
        } else if (sqlState.startsWith("40")) { // 事务回滚/死锁
            return ExceptionType.DEADLOCK;
        } else if (sqlState.startsWith("28")) { // 访问权限
            return ExceptionType.PERMISSION;
        }
        
        // MySQL特定错误码
        switch (errorCode) {
            case 1045: // Access denied
                return ExceptionType.PERMISSION;
            case 1062: // Duplicate entry
            case 1452: // Foreign key constraint
                return ExceptionType.CONSTRAINT;
            case 1213: // Deadlock
                return ExceptionType.DEADLOCK;
            case 2003: // Can't connect
            case 2006: // MySQL server has gone away
                return ExceptionType.CONNECTION;
            case 1064: // SQL syntax error
                return ExceptionType.SYNTAX;
            default:
                return ExceptionType.UNKNOWN;
        }
    }

    /**
     * 记录异常分析日志
     */
    private static void logExceptionAnalysis(Throwable exception, ExceptionType type, 
                                           String sql, String method) {
        try {
            String shortSql = sql != null && sql.length() > 100 ? 
                sql.substring(0, 100) + "..." : sql;
            
            log.warn("🔍 [异常分析] 类型: {} | 方法: {} | 异常: {} | 建议: {} | SQL: {}",
                type.getDescription(),
                method != null ? method : "未知",
                exception.getClass().getSimpleName(),
                type.getSuggestion(),
                shortSql != null ? shortSql : "未知");
                
        } catch (Exception e) {
            log.debug("记录异常分析日志失败", e);
        }
    }

    /**
     * 获取异常统计报告
     */
    public static String getStatisticsReport() {
        StringBuilder report = new StringBuilder();
        report.append("\n=== 数据库异常统计报告 ===\n");
        report.append("总异常数: ").append(totalExceptions.get()).append("\n\n");
        
        if (exceptionStats.isEmpty()) {
            report.append("暂无异常记录\n");
            return report.toString();
        }
        
        // 按异常次数排序
        exceptionStats.entrySet().stream()
            .sorted((e1, e2) -> Long.compare(e2.getValue().getCount(), e1.getValue().getCount()))
            .forEach(entry -> {
                String key = entry.getKey();
                ExceptionStats stats = entry.getValue();
                
                report.append(String.format("%-30s | 次数: %6d | 首次: %s | 最近: %s\n",
                    key,
                    stats.getCount(),
                    stats.getFirstOccurrence() != null ? 
                        stats.getFirstOccurrence().toString().substring(5, 16) : "未知",
                    stats.getLastOccurrence() != null ? 
                        stats.getLastOccurrence().toString().substring(5, 16) : "未知"));
                        
                if (stats.getLastMethod() != null) {
                    report.append("    最近方法: ").append(stats.getLastMethod()).append("\n");
                }
                if (stats.getLastSql() != null && stats.getLastSql().length() > 0) {
                    String shortSql = stats.getLastSql().length() > 80 ? 
                        stats.getLastSql().substring(0, 80) + "..." : stats.getLastSql();
                    report.append("    最近SQL: ").append(shortSql).append("\n");
                }
                report.append("\n");
            });
        
        return report.toString();
    }

    /**
     * 检查是否需要告警
     */
    public static boolean shouldAlert(ExceptionType type, int threshold) {
        String typePrefix = type.name() + ":";
        
        long recentCount = exceptionStats.entrySet().stream()
            .filter(entry -> entry.getKey().startsWith(typePrefix))
            .mapToLong(entry -> {
                ExceptionStats stats = entry.getValue();
                // 统计最近5分钟的异常
                LocalDateTime fiveMinutesAgo = LocalDateTime.now().minusMinutes(5);
                return stats.getLastOccurrence() != null && 
                       stats.getLastOccurrence().isAfter(fiveMinutesAgo) ? 
                       stats.getCount() : 0;
            })
            .sum();
            
        return recentCount >= threshold;
    }

    /**
     * 清理过期的统计信息
     */
    public static void cleanupOldStats() {
        try {
            LocalDateTime oneHourAgo = LocalDateTime.now().minusHours(1);
            
            exceptionStats.entrySet().removeIf(entry -> {
                ExceptionStats stats = entry.getValue();
                return stats.getLastOccurrence() != null && 
                       stats.getLastOccurrence().isBefore(oneHourAgo);
            });
            
            log.debug("清理过期异常统计完成，当前记录数: {}", exceptionStats.size());
            
        } catch (Exception e) {
            log.warn("清理异常统计时发生错误", e);
        }
    }

    /**
     * 重置所有统计信息
     */
    public static void resetStatistics() {
        exceptionStats.clear();
        totalExceptions.set(0);
        log.info("异常统计信息已重置");
    }

    /**
     * 获取特定类型的异常建议
     */
    public static String getSuggestion(ExceptionType type) {
        return type.getSuggestion();
    }

    /**
     * 获取异常的详细分析
     */
    public static String getDetailedAnalysis(Throwable exception) {
        if (exception == null) {
            return "无异常信息";
        }
        
        StringBuilder analysis = new StringBuilder();
        ExceptionType type = classifyException(exception);
        
        analysis.append("异常类型: ").append(type.getDescription()).append("\n");
        analysis.append("异常类: ").append(exception.getClass().getSimpleName()).append("\n");
        analysis.append("异常消息: ").append(exception.getMessage() != null ? exception.getMessage() : "无").append("\n");
        analysis.append("解决建议: ").append(type.getSuggestion()).append("\n");
        
        // 如果是SQL异常，添加更多信息
        if (exception instanceof SQLException) {
            SQLException sqlEx = (SQLException) exception;
            analysis.append("SQL状态码: ").append(sqlEx.getSQLState()).append("\n");
            analysis.append("错误代码: ").append(sqlEx.getErrorCode()).append("\n");
        }
        
        return analysis.toString();
    }
}
